package demo.spark.hurypoint.filedealer

import java.io.FileWriter

import demo.spark.hurypoint.filedealer.ParquetDealer.getTimeSequences

object SqlFileTouch {



  def main(args: Array[String]): Unit = {
    //genericTblSql();
    genericTblSqlByParquet();
  }


  def genericTblSql():Unit = {
    val timeSeqs:Seq[String] = getTimeSequences("2021-06-04", "2021-07-15");

    var file : java.io.File = new java.io.File("/Users/icasue/Desktop/bury_point/hive-tab-init.sql");
    if(file exists()){
      file.delete();
    }
    var writer: FileWriter = new FileWriter(file);

    val sqlTemp: String = new StringBuilder()
      .append("create external table bury_point_%s(dt string,vin string,osver string,model string,appver string,event_type string,page_name string, ctrl_name string,event_name string,args string)")
      .append("\n")
      .append("row format delimited ")
      .append("\n")
      .append("fields terminated by ';'")
      .append("\n")
      .append("location '/hive/external/db.icasue/bury_point_%s';")
      .append("\n")
      .append("alter table bury_point_%s set tblproperties('skip.header.line.count'='1');")
      .append("\n")
      .append("load data local inpath '/Users/icasue/Desktop/bury_point/csv_json_clear/%s.csv/*.csv' overwrite into table bury_point_%s;")
      .append("\n\n")
      .toString();

    for(timeUnit:String <- timeSeqs){
      val timeSimple = timeUnit.replace("-", "")
      var sql: String = sqlTemp.format(timeSimple,timeSimple,timeSimple,timeUnit,timeSimple);
      writer.write(sql)
      writer.flush();
    }

    writer.close();
  }

  def genericTblSqlByParquet():Unit = {
    val timeSeqs:Seq[String] = getTimeSequences("2021-06-02", "2021-07-15");

    var file : java.io.File = new java.io.File("/Users/icasue/Desktop/bury_point/hive-tab-parquet-init.sql");
    if(file exists()){
      file.delete();
    }
    var writer: FileWriter = new FileWriter(file);

    val sqlTemp: String = new StringBuilder()
      .append("create external table bury_point_%s(dt string,vin string,osver string,model string,appver string,event_type string,page_name string, ctrl_name string,event_name string,args string)")
      .append("\n")
      .append("stored as parquet")
      .append("\n")
      .append("location '/hive/external/db.icasue2/bury_point_%s';")
      .append("\n")
      .append("load data local inpath '/Users/icasue/Desktop/bury_point/parquet_json_clear/%s.parquet/*.parquet' overwrite into table bury_point_%s;")
      .append("\n\n")
      .toString();

    for(timeUnit:String <- timeSeqs){
      val timeSimple = timeUnit.replace("-", "")
      var sql: String = sqlTemp.format(timeSimple,timeSimple,timeUnit,timeSimple);
      writer.write(sql)
      writer.flush();
    }

    writer.close();
  }
}
